package com.eascs.admin.o2o.loan.service;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import com.alibaba.dubbo.config.annotation.Service;
import com.eascs.admin.common.service.BaseSevice;
import com.eascs.admin.common.utils.StringUtils;
import com.eascs.admin.common.vo.LoanEntity;
import com.eascs.admin.common.vo.LoanInfo;
import com.eascs.admin.common.vo.OverdueReportPO;
import com.eascs.admin.common.vo.RefundLine;
import com.eascs.admin.common.vo.WeChatParams;
import com.eascs.admin.loanapplyoplog.LoanapplyOpLog;
import com.eascs.admin.o2o.backfee.entity.O2OComReceiveFee;
import com.eascs.admin.o2o.bill.BillCode;
import com.eascs.admin.o2o.detail.dao.DataCenterDao;
import com.eascs.admin.o2o.internal.entity.WfTypeStatus;
import com.eascs.admin.o2o.internal.util.BaseUserUtil;
import com.eascs.admin.o2o.internal.util.StatusContexts;
import com.eascs.admin.o2o.loan.action.O2oBankInfoForm;
import com.eascs.admin.o2o.loan.action.O2oContractForm;
import com.eascs.admin.o2o.loan.dao.LoanapplyOpLogDao;
import com.eascs.admin.o2o.loan.dao.O2oLoanUserDao;
import com.eascs.admin.o2o.loan.dao.O2oLoanapplyDao;
import com.eascs.admin.o2o.loan.dao.O2oNumDao;
import com.eascs.admin.o2o.loan.dao.OaLoanApplyDao;
import com.eascs.admin.o2o.loan.entity.LoanApplyFileForm;
import com.eascs.admin.o2o.loan.entity.O2oBankInfoEntity;
import com.eascs.admin.o2o.loan.entity.O2oBankTransLogFrom;
import com.eascs.admin.o2o.loan.entity.O2oContractEntity;
import com.eascs.admin.o2o.loan.entity.O2oLoanUserEntity;
import com.eascs.admin.o2o.loan.entity.O2oLoanapplyEntity;
import com.eascs.admin.o2o.loan.entity.O2oOpenBankAccountEntity;
import com.eascs.admin.o2o.loan.entity.OaBillCodeEntity;
import com.eascs.admin.o2o.loan.entity.OaLoanApplyEntity;
import com.eascs.admin.o2o.loan.entity.OaLoanProvinceEntity;
import com.eascs.admin.o2o.loan.form.AddLoanapplyForm;
import com.eascs.admin.o2o.loanAccount.entity.O2oLoanapplyAccountEntity;
import com.eascs.admin.o2o.zx.entity.O2oZxEntity;
import com.eascs.admin.recordlog.constant.O2oLogType;
import com.eascs.afterloan.dao.O2oReportAfterDao;
import com.eascs.app.dao.DaoException;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.crm.nsEditDetail.entity.CustOpLogEntity;
import com.eascs.crm.nsEditDetail.entity.CustomerBasicEntity;
import com.eascs.crm.nsEditDetail.service.CustOpLogService;
import com.eascs.crm.nsEditDetail.service.CustomerInfoService;
import com.eascs.datalist.entity.O2oDataListEntity;
import com.eascs.gaczcontract.entity.CustomerBasicInfo;
import com.eascs.loan.dao.O2oLoanDao;
import com.eascs.loan.form.O2oLoanForm;
import com.eascs.web.sso.User;
import com.eascs.web.sso.UserHolder;
import com.eascs.workflow.common.vo.ModelResult;
import com.eascs.workflow.form.MQFrom;
import com.eascs.workflow.form.SDForm;
import com.fasterxml.jackson.databind.ObjectMapper;

import jodd.util.StringUtil;

@Service
public class O2oLoanapplyService extends BaseSevice {
	private final Logger logger = LoggerFactory.getLogger(O2oLoanapplyService.class);
	@Autowired
	private O2oLoanapplyDao loanDao;
	@Autowired
	private O2oReportAfterDao o2oReportAfterDao;

	@Autowired
	private BillCode billCode;

	@Autowired
	private O2oNumDao o2oNumDao;

	@Autowired
	private BaseUserUtil userUtils;

	@Autowired
	LoanapplyOpLogDao loanapplyOpLogDao;

	@Autowired
	OaLoanApplyDao oaApplyDao;

	@Autowired
	O2oLoanDao o2oLoanDao;

	@Autowired
	private O2oLoanUserDao o2oLoanUserDao;
	@Autowired
	private DataCenterDao dataCenterDao;
	@Autowired
	private CustomerInfoService customerInfoService;
	@Autowired
	CustOpLogService custOpLogService;
	

	public PageResult<Object[]> findByPage(int pageSizee, int pageIndex, O2oLoanapplyEntity o2oLoanapplyEntity) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, pageSizee, pageIndex);
		return loanDao.findDataWithPage(pageQuery);
	}

	public O2oLoanapplyEntity findById(String id) {
		logger.debug("find loan by id,id={}", id);
		return loanDao.getById(O2oLoanapplyEntity.class, id);
	}
	
	public O2oZxEntity findByBillcode(String billCode) {
		logger.debug("find loan by id,id={}", billCode);
		String sql = "select * from o2o_zx where bill_code=?";
		return loanDao.findEntity(sql, O2oZxEntity.class, sql);
	}
	
	public O2oLoanapplyEntity findByIdDetail(String id) {
		logger.debug("find loan by id,id={}", id);
		String sql = "SELECT ol.* from o2o_loanapply ol WHERE  ol.loanapply_no = ? ";
		return loanDao.findEntity(sql, O2oLoanapplyEntity.class, id);
	}
	
	/**
	 * @Title: findJxbLoanByIdcardNum
	 * @Description: 根据证件号查询送审银行为江西银行，并且没有放款的单据
	 * @author 张庆奇
	 * @param idNum
	 * @return
	 * @return List<O2oLoanapplyEntity>
	 * @throws
	 * @date 2017年6月7日 下午2:42:14
	 */
	public List<O2oLoanapplyEntity> findJxbLoanByIdcardNum(String idNum){

		
		return loanDao.findJxbLoanByIdcardNum(idNum);
	}

	public O2oLoanapplyEntity findContinueById(String id) {
		logger.debug("find loan by id,id={}", id);
		return loanDao.getById(O2oLoanapplyEntity.class, id);
	}
	
	public O2oLoanapplyEntity fingByBillCode(String billCode) {
		String sql = "SELECT * from o2o_loanapply  WHERE bill_code = ? ";
		return loanDao.findEntity(sql, O2oLoanapplyEntity.class, billCode);
	}
	
	public O2oLoanapplyEntity fingByBillCodeDetail(String billCode) {
		String sql = "SELECT ol.* from o2o_loanapply ol WHERE ol.bill_code = ? ";
		return loanDao.findEntity(sql, O2oLoanapplyEntity.class, billCode);
	}
	
	public O2oLoanUserEntity fingByloanId(String loanId) {
		String sql = "SELECT * from o2o_loan_user  WHERE loan_id = ? ";
		return loanDao.findEntity(sql, O2oLoanUserEntity.class, loanId);
	}
	
	public O2OComReceiveFee findExpandByBillCode(String billCode) {
		String sql = "SELECT * from o2o_loanapply_expand  WHERE bill_code = ? ";
		return loanDao.findEntity(sql, O2OComReceiveFee.class, billCode);
	}
	
	public O2oLoanapplyEntity findBillCode(String billCode) {
		String sql = "SELECT * from o2o_loanapply  WHERE bill_code = ? and (data_source is null or data_source<>'2') ";
		return loanDao.findEntity(sql, O2oLoanapplyEntity.class, billCode);
	}
	public O2oLoanapplyEntity findBillCode1(String billCode) {
		String sql = "SELECT * from o2o_loanapply  WHERE bill_code = ?";
		return loanDao.findEntity(sql, O2oLoanapplyEntity.class, billCode);
	}
	public O2oBankInfoForm findO2oBankInfoEntity(String loanapplyNo) {
		String sqlUSer = "select olu.user_full_name from o2o_loan_user olu where loan_id=? and loanapply_no=?";
		O2oLoanapplyEntity o2oLoanapplyEntity = loanDao.getById(O2oLoanapplyEntity.class, loanapplyNo);
		O2oLoanUserEntity o2oLoanUserEntity = loanDao.findEntity(sqlUSer, O2oLoanUserEntity.class, o2oLoanapplyEntity.getLoanId(), loanapplyNo);
		O2oBankInfoForm o2oBankInfoForm = new O2oBankInfoForm();
		o2oBankInfoForm.setLoanapplyno(o2oLoanapplyEntity.getLoanapplyNo());
		o2oBankInfoForm.setReferId(o2oLoanapplyEntity.getLoanapplyNo());
		o2oBankInfoForm.setBankCardNo(o2oLoanapplyEntity.getBankAccount());
		if (null != o2oLoanUserEntity) {
			o2oBankInfoForm.setBankUserName(o2oLoanUserEntity.getUserFullName());
		}
		o2oBankInfoForm.setBankZhiName(o2oLoanapplyEntity.getReceiveBank());
		o2oBankInfoForm.setLoanId(o2oLoanapplyEntity.getLoanId());
		o2oBankInfoForm.setBankLoanName(o2oLoanapplyEntity.getBankLoanName());
		o2oBankInfoForm.setContractFlag(o2oLoanapplyEntity.getContractFlag());
		return o2oBankInfoForm;
	}

	public O2oContractForm findO2oContractForm(O2oContractForm o2oContractForm) {
		String sql = "select olc.* from o2o_loan_contract olc where olc.loanapplyno=?";
		String sqlFindBankLoanName = "select bank_loan_name from o2o_loanapply where loanapply_no=?";
		String bankLoanName = loanDao.findString(sqlFindBankLoanName, o2oContractForm.getReferId());
		O2oContractEntity o2oContractEntity = loanDao.findEntity(sql, O2oContractEntity.class, o2oContractForm.getReferId());
		O2oBankInfoEntity o2oLoanapplyEntity = loanDao.getById(O2oBankInfoEntity.class, o2oContractForm.getReferId());
		if (o2oContractEntity != null) {
			o2oContractForm.setAgency(o2oContractEntity.getAgency());
			o2oContractForm.setbAddress(o2oContractEntity.getbAddress());
			o2oContractForm.setbUnits(o2oContractEntity.getbUnits());
			o2oContractForm.setId(o2oContractEntity.getId());
			o2oContractForm.setIsLock(o2oContractEntity.getIsLock());
			o2oContractForm.setIsPay(o2oContractEntity.getIsPay());
			o2oContractForm.setIsSign(o2oContractEntity.getIsSign());
			o2oContractForm.setLoanApplyNo(o2oContractEntity.getLoanApplyNo());
			o2oContractForm.setReferId(o2oContractEntity.getLoanApplyNo());
			o2oContractForm.setContractNo(o2oContractEntity.getContractNo());
		}
		if (o2oLoanapplyEntity != null) {
			o2oContractForm.setOpenAccountBank(o2oLoanapplyEntity.getOpenAccountBank());
			o2oContractForm.setBankCardNo(o2oLoanapplyEntity.getBankAccount());
			o2oContractForm.setBankZhiName(o2oLoanapplyEntity.getReceiveBank());
		}
		if (!StringUtil.isEmpty(bankLoanName))
			o2oContractForm.setBankLoanName(bankLoanName);
		return o2oContractForm;
	}

	public O2oContractEntity insertO2oContractForm(O2oContractEntity o2oContractEntity) {
		int i = loanDao.insert(o2oContractEntity);
		if (i > 0) {
			return o2oContractEntity;
		}
		throw new DaoException("insert o2o_loan_contract failed");
	}

	public int updateO2oContractForm(O2oContractEntity o2oContractEntity, O2oContractForm o2oContractForm) {
		O2oOpenBankAccountEntity o2oOpenBankAccountEntity = new O2oOpenBankAccountEntity();
		o2oOpenBankAccountEntity.setLoanapplyNo(o2oContractForm.getLoanApplyNo());
		o2oOpenBankAccountEntity.setBankLoanName(o2oContractForm.getBankLoanName());
		loanDao.updateById(o2oOpenBankAccountEntity);
		return loanDao.updateById(o2oContractEntity);
	}

	public O2oBankInfoEntity insertO2oBankInfoEntity(O2oBankInfoEntity o2oBankInfoEntity) {
		int i = loanDao.insert(o2oBankInfoEntity);
		if (i > 0) {
			return o2oBankInfoEntity;
		}
		throw new DaoException("insert o2o_bank_info failed");
	}

	public int updateContractFlag(MQFrom mqFrom) {
		O2oLoanapplyEntity entity = findById(mqFrom.getLoanapplyno());
		entity.setContractFlag(mqFrom.getContractFlag());
		return loanDao.updateById(entity);
	}

	public int updateO2oBankInfoEntity(O2oLoanapplyEntity o2oloanapply) {
		O2oLoanapplyEntity entity = findById(o2oloanapply.getLoanapplyNo());
		ObjectMapper mapper = new ObjectMapper();
		try {
			saveLog(UserHolder.getUser().getCname(), O2oLogType.LOAN_STATUS_S, mapper.writeValueAsString(entity), mapper.writeValueAsString(o2oloanapply));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return loanDao.updateById(o2oloanapply);
	}

	public PageResult<Object[]> findO2oDataListEntityById(String loanapply_no) {
		String sql = "select datalist_no,datalist_name,original_name,creat_or,creat_date,update_time,is_delete from  o2o_datalist where is_delete=? and loanapply_no=? ORDER BY update_time desc";
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		pageQuery.addParam(StatusContexts.O2O_NO);
		pageQuery.addParam(loanapply_no);
		pageQuery.setQuerySql(sql);
		return loanDao.findDataWithPage(pageQuery);
	}
	
	/**
	 * 补充征信附件信息
	 * @param loanapply_no
	 * @return
	 */
	public PageResult<Object[]> supplementFile(String loanapply_no) {
		String sql = "select datalist_no,datalist_name,original_name,creat_or,creat_date,update_time,is_delete from  o2o_datalist where is_delete=? and loanapply_no in("+loanapply_no+") ORDER BY update_time desc";
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		pageQuery.addParam(StatusContexts.O2O_NO);
//		pageQuery.addParam(loanapply_no);
		pageQuery.setQuerySql(sql);
		return loanDao.findDataWithPage(pageQuery);
	}
	
	/**
	 * @Title: findFqZxbgfj
	 * @Description: 查询征信报告附件
	 * @author 张庆奇
	 * @param userZxId
	 * @param mateZxId
	 * @return
	 * @return PageResult<Object[]>
	 * @throws
	 * @date 2017年9月7日 下午2:28:37
	 */
	public PageResult<Object[]> findFqZxbgfj(String userZxId,String mateZxId) {
		String sql = "select datalist_no,datalist_name,original_name,creat_or,creat_date,update_time,is_delete from  o2o_datalist where is_delete=?";
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		pageQuery.addParam(StatusContexts.O2O_NO);
		if (StringUtils.isNotEmpty(userZxId)&&StringUtils.isNotEmpty(mateZxId)) {
			sql = sql + " and loanapply_no in('" + userZxId + "','" + mateZxId + "')";
		} else {
			if (StringUtils.isNotEmpty(userZxId)) {
				sql = sql + " and loanapply_no=? ";
				pageQuery.addParam(userZxId);
			}
			
			if (StringUtils.isNotEmpty(mateZxId)) {
				sql = sql + " and loanapply_no=? ";
				pageQuery.addParam(mateZxId);
			}

		}


		sql = sql + " ORDER BY update_time desc";
		pageQuery.setQuerySql(sql);
		return loanDao.findDataWithPage(pageQuery);
	}

	/**
	 * 根据code查询不同 附件类型
	 * 
	 * @param loanapply_no
	 * @param code
	 * @return
	 */
	public PageResult<Object[]> findO2oDataListEntityById(String loanapply_no, String code) {
		String sql = "select datalist_no,datalist_name,original_name,creat_or,creat_date,update_time from  o2o_datalist where is_delete=? and loanapply_no=? and datalist_code in (" + code + ") ORDER BY update_time desc";
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		pageQuery.addParam(StatusContexts.O2O_NO);
		pageQuery.addParam(loanapply_no);
//		pageQuery.addParam(code);
		pageQuery.setQuerySql(sql);
		return loanDao.findDataWithPage(pageQuery);
	}
	
	/**
	 * 根据code查询不同 附件类型
	 * 
	 * @param loanapply_no
	 * @param code
	 * @return
	 */
	public PageResult<Object[]> findO2oDataListEntityById(String loanapply_no, String bzjdata, String bzjGlfDfpz) {
		String sql = "select datalist_no,datalist_name,original_name,creat_or,creat_date,update_time from  o2o_datalist where is_delete=? and loanapply_no=? and datalist_code in (?,?) ORDER BY update_time desc";
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		pageQuery.addParam(StatusContexts.O2O_NO);
		pageQuery.addParam(loanapply_no);
		pageQuery.addParam(bzjdata);
		pageQuery.addParam(bzjGlfDfpz);
		pageQuery.setQuerySql(sql);
		return loanDao.findDataWithPage(pageQuery);
	}

	/**
	 * 内审查询 附件类型
	 * 
	 * @param loanapply_no
	 * @param code
	 * @return
	 */
	public PageResult<Object[]> findO2oDataListEntityByNS(String loanapply_no) {
		String sql = "select datalist_no,datalist_name,original_name,creat_or,creat_date,update_time from  o2o_datalist where is_delete=? and loanapply_no=? and datalist_code in ('IDC','REFUNDCONTRACTBAK','GUARANTEE','LOANAPPLY','MQVIDEO','MQPHOTO') ORDER BY update_time desc";
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		pageQuery.addParam(StatusContexts.O2O_NO);
		pageQuery.addParam(loanapply_no);
		pageQuery.setQuerySql(sql);
		return loanDao.findDataWithPage(pageQuery);
	}

	public List<O2oDataListEntity> findCodeList(String loanapply_no, String code) {
		String sql = "select datalist_no,datalist_name,original_name,creat_or,creat_date from  o2o_datalist where is_delete=? and loanapply_no=? and datalist_code=? ORDER BY update_time desc";
		return loanDao.findEntitys(sql, O2oDataListEntity.class, StatusContexts.O2O_NO, loanapply_no, code);
	}

	public List<Object[]> getUnitsList(String name) {
		String sql = "SELECT UNITNO,NAMECN,CONCAT(UNITCODE,'|',NAMECNSHORT)  FROM HR_UNITS WHERE 1=1  AND NAMECNSHORT LIKE ? ORDER BY UNITNO DESC  LIMIT 10";

		return loanDao.findObjectsList(sql, "%" + name + "%");
	}

	public List<Object[]> getOperatorList(String name) {
		String sql = "SELECT EMPLOYEENO, EMPLOYEENAMECN, CONCAT(EMPLOYEECODE,'|', EMPLOYEEACCOUNT)  FROM HR_EMPLOYEE WHERE 1=1  AND EMPLOYEENAMECN LIKE ? ORDER BY  EMPLOYEENO DESC LIMIT 10";

		return loanDao.findObjectsList(sql, "%" + name + "%");
	}

	public List<Object[]> getCustomerList(String name) {
		String sql = "SELECT CNO,CNAME,CCODE FROM eaba_customer where 1=1 AND CNAME LIKE ? ORDER BY  CNO DESC LIMIT 10 ";

		return loanDao.findObjectsList(sql, "%" + name + "%");
	}

	public int fromDatalistGetLoanApplyWfType(String datalistno) {
		String sql = "SELECT COUNT(*) AS WF_TYPE FROM O2O_LOANAPPLY OL JOIN O2O_DATALIST OD ON OL.LOANAPPLY_NO = OD.LOANAPPLY_NO AND OD.DATALIST_NO = ? AND OL.WF_TYPE NOT IN('" + StatusContexts.O2O_NEW + "','" + StatusContexts.O2O_ZX_END + "')";
		String wf_type = loanDao.findString(sql, datalistno);
		int i = 0;
		if (!StringUtils.isEmpty(wf_type)) {
			i = Integer.parseInt(wf_type);
		}
		return i;
	}

	@Transactional(transactionManager = "o2oTransactionManager")
	public int updateSql(String sql, Object... params) {
		return loanDao.execute(sql, params);
	}

	public int updateFormApplyLoan(O2oLoanapplyEntity formApplyLoan) {
		O2oLoanapplyEntity entity = findById(formApplyLoan.getLoanapplyNo());
		ObjectMapper mapper = new ObjectMapper();
		if (entity != null) { // 修改
			try {
				saveLog(UserHolder.getUser().getCname(), O2oLogType.LOAN_STATUS_NEW, "", mapper.writeValueAsString(formApplyLoan));
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else { // 新增
			try {
				saveLog(UserHolder.getUser().getCname(), O2oLogType.LOAN_STATUS_Z, mapper.writeValueAsString(entity), mapper.writeValueAsString(formApplyLoan));
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return loanDao.updateById(formApplyLoan);
	}

	@Transactional(transactionManager = "o2oTransactionManager")
	public int updateEntityById(O2oLoanapplyEntity loanEntity) {
		return loanDao.updateById(loanEntity);
	}

	@Transactional(transactionManager = "o2oTransactionManager")
	public int updateEntityById(O2oLoanapplyEntity loanEntity, WfTypeStatus w) {
		return (loanDao.updateById(loanEntity)<0 || loanDao.updateById(w)<0?0:1);
	}

	@Transactional(transactionManager = "o2oTransactionManager")
	public int deleteEntityById(O2oDataListEntity o2oDataListEntity) {
		return loanDao.deleteByIdWithEntity(o2oDataListEntity);
	}

	/**
	 * 新增终端贷 无配偶关系
	 * 
	 * @param loanEntity
	 * @param loanUserEntity
	 * @return
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public void saveEntityById(O2oLoanapplyEntity loanEntity, O2oLoanUserEntity loanUserEntity,O2oLoanapplyAccountEntity o2oLoanapplyAccountEntity) {
		ObjectMapper mapper = new ObjectMapper();
		try {
			saveLog(UserHolder.getUser().getCname(), O2oLogType.LOAN_STATUS_NEW, mapper.writeValueAsString(null), mapper.writeValueAsString(loanEntity));
		} catch (Exception e) {
			e.printStackTrace();
		}
		loanDao.insert(loanEntity);
		loanUserEntity.setLoanappNo(loanEntity.getLoanapplyNo());
		loanDao.insert(loanUserEntity);
		o2oLoanapplyAccountEntity.setLoanapplyNo(loanEntity.getLoanapplyNo());
		o2oLoanapplyAccountEntity.setStatus(StatusContexts.O2O_ZERO);
		o2oLoanapplyAccountEntity.setJqstatus(StatusContexts.O2O_ZERO);
		loanDao.insert(o2oLoanapplyAccountEntity);
		CustomerBasicEntity customerBasicLoder = new CustomerBasicEntity();
		customerBasicLoder.setLoan_user_id(loanUserEntity.getId());
		customerBasicLoder.setMarital_status((int)loanUserEntity.getO2oLoanUserEntityExp().getMaritalStatus());
		customerBasicLoder.setLicense_expiration_date(loanUserEntity.getO2oLoanUserEntityExp().getLicenseExpirationDate());
		customerInfoService.saveEntity(customerBasicLoder);
		//return loanDao.insert(loanUserEntity);
	}

	/**
	 * 续贷终端贷 无配偶关系
	 * 
	 * @param loanEntity
	 * @param loanUserEntity
	 * @return
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public void saveContinueEntity(O2oLoanapplyEntity loanEntity, O2oLoanUserEntity loanUserEntity, AddLoanapplyForm addloanapplyForm) {
		ObjectMapper mapper = new ObjectMapper();
		try {
			saveLog(UserHolder.getUser().getCname(), O2oLogType.LOAN_STATUS_NEW, mapper.writeValueAsString(null), mapper.writeValueAsString(loanEntity));
		} catch (Exception e) {
			e.printStackTrace();
		}
		loanDao.insert(loanEntity);
		loanUserEntity.setLoanappNo(loanEntity.getLoanapplyNo());
		loanDao.insert(loanUserEntity);
		if (StatusContexts.DATA_SOURCE_NEW.equals(addloanapplyForm.getCodeType())) {
			if (addloanapplyForm.getBillCode() != null) {
				O2oLoanapplyEntity loanapply = loanDao.queryBillCode(addloanapplyForm.getBillCode().trim());
				loanapply.setIsrenew(StatusContexts.O2O_YES);
				loanDao.updateById(loanapply);
			}
		}
	}

	/**
	 * 新增终端贷
	 * 
	 * @param loanEntity
	 * @param loanUserEntity
	 * @param loanUserSpouseEntity
	 * @return
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public void saveEntityById(O2oLoanapplyEntity loanEntity, O2oLoanUserEntity loanUserEntity, O2oLoanUserEntity loanUserSpouseEntity,O2oLoanapplyAccountEntity o2oLoanapplyAccountEntity) {
		ObjectMapper mapper = new ObjectMapper();
		try {
			saveLog(UserHolder.getUser().getCname(), O2oLogType.LOAN_STATUS_NEW, mapper.writeValueAsString(null), mapper.writeValueAsString(loanEntity));
		} catch (Exception e) {
			e.printStackTrace();
		}
		loanDao.insert(loanEntity);
		
		loanUserEntity.setLoanappNo(loanEntity.getLoanapplyNo());
		loanDao.insert(loanUserEntity);
		
		o2oLoanapplyAccountEntity.setLoanapplyNo(loanEntity.getLoanapplyNo());
		o2oLoanapplyAccountEntity.setStatus(StatusContexts.O2O_ZERO);
		o2oLoanapplyAccountEntity.setJqstatus(StatusContexts.O2O_ZERO);
		loanDao.insert(o2oLoanapplyAccountEntity);
		
		CustomerBasicEntity customerBasicLoder = new CustomerBasicEntity();
		customerBasicLoder.setLoan_user_id(loanUserEntity.getId());
		customerBasicLoder.setMarital_status((int)loanUserEntity.getO2oLoanUserEntityExp().getMaritalStatus());
		customerBasicLoder.setLicense_expiration_date(loanUserEntity.getO2oLoanUserEntityExp().getLicenseExpirationDate());
		customerInfoService.saveEntity(customerBasicLoder);
		
		//存了o2o_loan_user之后在存一份到customer_basic_info表中
		// customerInfo
		loanUserSpouseEntity.setLoanappNo(loanEntity.getLoanapplyNo());
		
		loanDao.insert(loanUserSpouseEntity);
		
		CustomerBasicEntity customerBasicSpouse = new CustomerBasicEntity();
		customerBasicSpouse.setLoan_user_id(loanUserSpouseEntity.getId());
		customerBasicSpouse.setMarital_status((int)loanUserSpouseEntity.getO2oLoanUserEntityExp().getMaritalStatus());
		customerBasicSpouse.setLicense_expiration_date(loanUserSpouseEntity.getO2oLoanUserEntityExp().getLicenseExpirationDate());
		customerInfoService.saveEntity(customerBasicSpouse);
		
	}

	public int saveSponsor(O2oLoanUserEntity loanUserEntity){
		return  loanDao.insert(loanUserEntity);
	}
	
	/**
	 * 续贷终端贷
	 * 
	 * @param loanEntity
	 * @param loanUserEntity
	 * @param loanUserSpouseEntity
	 * @return
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int saveContinueEntitys(O2oLoanapplyEntity loanEntity, O2oLoanUserEntity loanUserEntity, O2oLoanUserEntity loanUserSpouseEntity, AddLoanapplyForm loanapplyForm) {
		ObjectMapper mapper = new ObjectMapper();
		try {
			saveLog(UserHolder.getUser().getCname(), O2oLogType.LOAN_STATUS_NEW, mapper.writeValueAsString(null), mapper.writeValueAsString(loanEntity));
		} catch (Exception e) {
			e.printStackTrace();
		}
		logger.info("aaaaaaaaaaaaaaaaaaaaaaaaaa", loanEntity.getCreditstatus());
		loanDao.insert(loanEntity);
		loanUserEntity.setLoanappNo(loanEntity.getLoanapplyNo());
		loanDao.insert(loanUserEntity);
		loanUserSpouseEntity.setLoanappNo(loanEntity.getLoanapplyNo());
		// 续贷修改上笔贷款是否续贷
		if (StatusContexts.DATA_SOURCE_NEW.equals(loanapplyForm.getCodeType())) {
			if (loanapplyForm.getBillCode() != null) {
				O2oLoanapplyEntity loanapply = loanDao.queryBillCode(loanapplyForm.getBillCode().trim());
				loanapply.setIsrenew(StatusContexts.O2O_YES);
				loanDao.updateById(loanapply);
			}
		}
		return loanDao.insert(loanUserSpouseEntity);
	}

	/**
	 * 续贷方法
	 * 
	 * @param loanEntity
	 * @return
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int continueLoanInsert(O2oLoanapplyEntity loanEntity) {
		ObjectMapper mapper = new ObjectMapper();
		try {
			saveLog(UserHolder.getUser().getCname(), O2oLogType.LOAN_STATUS_NEW, mapper.writeValueAsString(null), mapper.writeValueAsString(loanEntity));
		} catch (Exception e) {
			e.printStackTrace();
		}
		return loanDao.insert(loanEntity);
	}

	@Transactional(transactionManager = "o2oTransactionManager")
	public String saveEntityReturnId(O2oLoanapplyEntity loanEntity) {
		O2oLoanapplyEntity entity = findById(loanEntity.getLoanapplyNo());
		ObjectMapper mapper = new ObjectMapper();
		if (entity != null) { // 修改
			try {
				saveLog(UserHolder.getUser().getCname(), O2oLogType.LOAN_STATUS_NEW, "", mapper.writeValueAsString(loanEntity));
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else { // 新增
			try {
				saveLog(UserHolder.getUser().getCname(), O2oLogType.LOAN_STATUS_Z, mapper.writeValueAsString(entity), mapper.writeValueAsString(loanEntity));
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		int insert_id = loanDao.insert(loanEntity);
		if (insert_id > 0) {
			return loanEntity.getLoanapplyNo();
		}
		throw new DaoException("insert loanapply fail");
	}

	public String getBillCode(String code, String type) {
		return billCode.getBillCode(code, type);
	}

	/**
	 * 
	 * get to loan apply count can insert data
	 * 
	 * @param loan_id
	 * @return
	 */
	public int getLoanApplyCount(String loan_id) {
		String sql = "SELECT COUNT(*) AS WF_TYPE FROM O2O_LOANAPPLY OL WHERE OL.LOAN_ID=? AND OL.WF_TYPE NOT IN ('" + StatusContexts.O2O_FK_END + "','" + StatusContexts.O2O_FK_OVER + "','" + StatusContexts.O2O_SB_FAIL + "')";
		O2oLoanapplyEntity loanEntity = loanDao.findEntity(sql, O2oLoanapplyEntity.class, loan_id);
		if (null != loanEntity.getWfType()) {
			int i = Integer.parseInt(loanEntity.getWfType());
			return i;
		}
		return 0;
	}

	/**
	 * 
	 * get to loan apply count can update data
	 * 
	 * @param loan_id
	 * @return
	 */
	public int getUpdateLoanApplyCount(String loan_id, String loanapplyNo) {
		String sql = "SELECT COUNT(*) AS WF_TYPE FROM O2O_LOANAPPLY OL WHERE OL.LOAN_ID=? AND OL.LOANAPPLY_NO=? AND OL.WF_TYPE NOT IN('" + StatusContexts.O2O_NEW + "','" + StatusContexts.O2O_ZX_END + "')";
		O2oLoanapplyEntity loanEntity = loanDao.findEntity(sql, O2oLoanapplyEntity.class, loan_id, loanapplyNo);
		if (null != loanEntity.getWfType()) {
			int i = Integer.parseInt(loanEntity.getWfType());
			return i;
		}
		return 0;
	}

	/**
	 * dubbo接口 crm贷款次数
	 * 
	 * @param loanId
	 * @return
	 */
	public LoanEntity queryLoanCount(LoanInfo loanInfo) throws Exception {
		LoanEntity loanEntity = new LoanEntity();
		String sql = "SELECT count(1) FROM O2O_LOANAPPLY OL WHERE OL.loan_id=? and OL.wf_type IN('O','HN')";
		int count = loanDao.findInteger(sql, loanInfo.getLoanId());
		count = count + o2oNumDao.getLoanapplyNum(loanInfo.getDocumentNo(), loanInfo.getDocumentType());
		loanEntity.setCount(count);
		return loanEntity;
	}

	/**
	 * 获取贷款次数 新系统和旧系统统计
	 * 
	 * @param loan_id
	 * @return
	 */
	public int findLoanAccount(String loanapplyNo, String loanId) {
		int count = 0;
		String sql = "SELECT count(1) FROM O2O_LOANAPPLY OL WHERE OL.loan_id=? and OL.wf_type IN('O','HN')";
		count = loanDao.findInteger(sql, loanId);
		O2oLoanUserEntity entity = loanDao.queryLoanpplyNo(loanapplyNo, loanId);
		if (entity != null) {
			count = count + o2oNumDao.getLoanapplyNum(entity.getDocumentNo(), entity.getDocumentType());
		}
		return count;
	}

	/**
	 * 根据身份证号码获取贷款次数
	 * 
	 * @param idcard
	 * @return
	 */
	public int findLoanAccountNEW(String idcard) {
		int count = 0;
		String sql = " select count(*) from basic_information_after_loan baf LEFT JOIN loan_base_info lb ON baf.bill_code = lb.bill_code where idcard = ? ";
		count = o2oReportAfterDao.findInteger(sql, idcard);
		return count;
	}

	/**
	 * 查询主借款 个人信息详情
	 * 
	 * @param loanapplyNo
	 * @param loanId
	 * @return
	 */
	public O2oLoanUserEntity findLoanUserEntity(String loanapplyNo, String loanId) {
		return loanDao.queryLoanpplyNo(loanapplyNo, loanId);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               查询主借款 个人信息详情 脱敏
	 *               </p>
	 * @author LMX
	 * @date 2018年3月6日 下午3:07:57
	 */
	public O2oLoanUserEntity findLoanUserEntity_sensitive(String loanapplyNo, String loanId) {
		return loanDao.queryLoanpplyNo_sensitive(loanapplyNo, loanId);
	}

	/**
	 * 查询配偶 贷款次数
	 * 
	 * @param loanapplyNo
	 * @return
	 */
	public int findMaterAccount(String loanapplyNo, String loanId) {
		int count = 0;
		String sql = "SELECT count(1) FROM O2O_LOANAPPLY OL WHERE OL.loan_id=? and OL.wf_type IN('O','HN')";
		count = loanDao.findInteger(sql, loanId);
		String loanapplySql = "select * from o2o_loan_user o where o.spouse_id=? and o.loanapply_no=? ";
		O2oLoanUserEntity entity = loanDao.findEntity(loanapplySql, O2oLoanUserEntity.class, loanId, loanapplyNo);
		if (entity != null) {
			logger.info("配偶编号：" + entity.getDocumentNo() + "配偶IDC：" + entity.getDocumentType());
			count = count + o2oNumDao.getLoanapplyNum(entity.getDocumentNo(), entity.getDocumentType());
		}
		return count;
	}

	public int queryLoanAccount(String idcard, String type) {
		return o2oNumDao.getLoanapplyNum(idcard, type);
	}

	/**
	 * 校验新系统是否可以 生成贷款单
	 * 
	 * @param id
	 * @return
	 */
	public int checkLoapply(String id) {
		// TODO 待优化SQL语句
		String sql = "select count(1) from o2o_loanapply t where t.loan_id=? and t.loan_type IN ('BK','BP','BP_JX','SG','YC','SC') and t.wf_type not in(?,?,?,?,?)";
		int i = loanDao.findInteger(sql, id, StatusContexts.O2O_SB_FAIL, StatusContexts.O2O_REPAY_END, StatusContexts.O2O_FK_OVER,StatusContexts.O2O_FK_END,StatusContexts.O2O_BANK_RUN);
		return i;
	}

	public int checkSpouse(String id) {
		String sql_j = "select count(1) from o2o_loanapply t where t.loan_id=? and t.loan_type IN ('BK','BP','BP_JX','SG','YC','SC') and t.wf_type not in(?,?,?,?,?)";
		int j = loanDao.findInteger(sql_j, id, StatusContexts.O2O_SB_FAIL, StatusContexts.O2O_REPAY_END, StatusContexts.O2O_FK_OVER,StatusContexts.O2O_FK_END,StatusContexts.O2O_BANK_RUN);
		return j;
	}

	/**
	 * 申请【终端贷】【品牌贷】时校验担保人是否有担保单未结清，如有未结清的担保单
	 */
	public int checksponsor(String id) {
		String sql_j = "select * from o2o_loanapply oly where loanapply_no in ("
				+ "select loanapply_no from o2o_loan_user olu where olu.loan_id =? and olu.sponsor_type = 'D'"
				+ ") and oly.wf_type not IN (?,?,?)";
		int j = loanDao.findInteger(sql_j, id,StatusContexts.O2O_SB_FAIL, StatusContexts.O2O_REPAY_END, StatusContexts.O2O_FK_OVER,StatusContexts.O2O_FK_END);
		return j;
	}
	
	/**
	 * 校验老系统是否可以 生成贷款单
	 * 
	 * @param id
	 * @return
	 */
	public int checkOALoapply(OaLoanApplyEntity entity) {
		// TODO 待优化SQL语句
		// 贷中
		StringBuffer sql_ = new StringBuffer();
		sql_.append(" SELECT COUNT(*) FROM EADB.O2O_LOANAPPLY OL WHERE OL.BILLSTATUSCREDIT IN ('NEW','RUN')");
		sql_.append(" AND OL.BILLSTATUS IN ('NEW','RUN') AND OL.ISLOCK='N' AND OL.LOANAPPLYNO_MAIN IS NULL AND OL.LOANAPPLYNO IN ");
		sql_.append("(SELECT OLO.LOANAPPLYNO FROM EADB.O2O_LOANER OLO WHERE OLO.IDCARD = ? AND OLO.IDCARDTYPE = ? )");
		int i_ = oaApplyDao.findInteger(sql_.toString(), entity.getLoanUserIdCardNo(), entity.getLoanUserIdCardType());
		if (i_ > 0) {
			return 2;
		} else {
			// 贷后
			StringBuffer sql = new StringBuffer();
			sql.append(" SELECT COUNT(*) FROM EADB.O2O_REFUND ORF JOIN EADB.O2O_LOANAPPLY OL ON ORF.LOANAPPLYNO = OL.LOANAPPLYNO ");
			sql.append(" WHERE  ORF.BILLSTATUS NOT IN (?,?) AND  ORF.ISLOCK ='N' AND OL.LOAN_TYPE = ? AND ORF.LOANAPPLYNO IN ");
			sql.append("(SELECT OLO.LOANAPPLYNO FROM EADB.O2O_LOANER OLO WHERE OLO.IDCARD = ? AND OLO.IDCARDTYPE = ? )");
			int i = oaApplyDao.findInteger(sql.toString(), StatusContexts.O2O_BANK_CLO, StatusContexts.O2O_BANK_REJ, entity.getLoanType(), entity.getLoanUserIdCardNo(), entity.getLoanUserIdCardType());
			if (i > 0) {
				return 1;
			}
		}
		return 0;
	}

	public int checkOaSpouse(OaLoanApplyEntity entity) {
		// 贷中
		StringBuffer sql_ = new StringBuffer();
		sql_.append(" SELECT COUNT(*) FROM EADB.O2O_LOANAPPLY OL WHERE OL.BILLSTATUSCREDIT IN ('NEW','RUN')");
		sql_.append(" AND OL.BILLSTATUS IN ('NEW','RUN') AND OL.ISLOCK='N' AND OL.LOANAPPLYNO IN ");
		sql_.append(" (SELECT OLO.LOANAPPLYNO FROM EADB.O2O_LOANER OLO WHERE (OLO.IDCARD,OLO.IDCARDTYPE) IN ");
		sql_.append(" (SELECT OLO2.COUPLEIDCARD,OLO2.COUPLEIDCARDTYPE FROM EADB.O2O_LOANER OLO2 ");
		sql_.append(" WHERE OLO2.IDCARD = ? AND OLO2.IDCARDTYPE = ? GROUP BY OLO2.COUPLEIDCARD,OLO2.COUPLEIDCARDTYPE)) ");
		int i_ = oaApplyDao.findInteger(sql_.toString(), entity.getLoanUserIdCardNo(), entity.getLoanUserIdCardType());
		if (i_ > 0) {
			return 2;
		} else {
			// 贷后
			StringBuffer sql_j = new StringBuffer();
			sql_j.append(" SELECT COUNT(*) FROM EADB.O2O_REFUND ORF JOIN EADB.O2O_LOANAPPLY OL ON ORF.LOANAPPLYNO = OL.LOANAPPLYNO ");
			sql_j.append(" WHERE  ORF.BILLSTATUS NOT IN (?,?) AND  ORF.ISLOCK ='N' AND OL.LOAN_TYPE = ? AND ORF.LOANAPPLYNO IN ");
			sql_j.append(" (select OLO.LOANAPPLYNO from eadb.o2o_loaner olo where (olo.IDCARD,OLO.IDCARDTYPE) in ");
			sql_j.append(" (select olo2.COUPLEIDCARD,olo2.COUPLEIDCARDTYPE from eadb.o2o_loaner olo2 ");
			sql_j.append(" where olo2.IDCARD = ? and olo2.IDCARDTYPE = ? GROUP BY olo2.COUPLEIDCARD,olo2.COUPLEIDCARDTYPE)) ");
			int j = oaApplyDao.findInteger(sql_j.toString(), StatusContexts.O2O_BANK_CLO, StatusContexts.O2O_BANK_REJ, entity.getLoanType(), entity.getLoanUserIdCardNo(), entity.getLoanUserIdCardType());
			if (j > 0) {
				return 1;
			}
		}
		return 0;
	}

	/**
	 * 修改工作流 状态
	 * 
	 * @param status
	 * @param loanapplyNo
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public void updateWorkFlowStatus(String status, String loanapplyNo) {
		if (status.equals(StatusContexts.O2O_ZX_BEGIN)) {
			String sql = "update o2o_loanapply o set o.wf_type=?,o.submit_date=?,o.submit_ro=?,o.end_ro=?,o.end_date=? where o.loanapply_no=?";
			loanDao.execute(sql, status, userUtils.getCreateTime(), userUtils.getCreateOr(), userUtils.getUpdateOr(), userUtils.getUpdateTime(), loanapplyNo);
		} else {
			String sql = "update o2o_loanapply o set o.wf_type=?,o.end_ro=?,o.end_date=? where o.loanapply_no=?";
			loanDao.execute(sql, status, userUtils.getUpdateOr(), userUtils.getUpdateTime(), loanapplyNo);
		}
	}

	/**
	 * 修改归档流程 状态
	 * 
	 * @param status
	 * @param loanapplyNo
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public void updateOnfileStatus(String status, String loanapplyNo) {
		String sql = "UPDATE O2O_LOANAPPLY OL SET OL.IS_PLACE_ON_FILE=?  WHERE OL.LOANAPPLY_NO=?";
		loanDao.execute(sql, status, loanapplyNo);
	}

	@Transactional(transactionManager = "o2oTransactionManager")
	public int saveEntityLog(LoanapplyOpLog loanEntity) {
		return loanapplyOpLogDao.insert(loanEntity);
	}

	@Transactional(transactionManager = "o2oTransactionManager")
	public void saveLog(String opPsn, String type, String up, String down) {
		// 保存操作日志
		LoanapplyOpLog log = new LoanapplyOpLog();
		try {
			log.setOpPsn(opPsn);
			log.setOpType(type);
			log.setOpTypeName(O2oLogType.getStringName(type));
			log.setOpDate(new Date());
			log.setOpContentUp(up);
			log.setOpContentDown(down);
			saveEntityLog(log);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 修改平台财务 前6个月进货平均和回款平均额度
	 * 
	 * @param sdForm
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public void updatePTCW(SDForm sdForm) {
		String sql = "update  o2o_loanapply o set o.ea_returned_amount=?,o.ea_purchaseamount=? where o.loanapply_no=?";
		loanDao.execute(sql, sdForm.getPaymentAvgAmount(), sdForm.getTakeAvgAmount(), sdForm.getLoanapplyNo());
	}

	/**
	 * 中行接口交互信息列表展示
	 * 
	 * @param bankTransLogFrom
	 *            查询条件实体
	 * @return
	 */
	public PageResult<Object[]> findO2oBankByPage(O2oBankTransLogFrom bankTransLogFrom) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, bankTransLogFrom.getPageSize(), bankTransLogFrom.getPageIndex());
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT OBTL.APP_NO,OBTL.BILL_CODE,OBTL.BANK_TYPE,OBTL.TYPE_NAME, ");
		sql.append(" (CASE WHEN OBTL.RESULT_TYPE = 1 THEN '失败' WHEN OBTL.RESULT_TYPE = 0 THEN '成功' END) AS RESULT_TYPE, ");
		sql.append(" OBTL.RESULT_DESC,OBTL.START_TIME,OBTL.END_TIME FROM O2O_BANK_TRANS_LOG OBTL ");
		sql.append(" WHERE 1=1 ");
		if (!StringUtil.isEmpty(bankTransLogFrom.getLoanApplyNo())) {
			sql.append(" AND OBTL.BILL_CODE LIKE ? ");
			pageQuery.addLikeParam(bankTransLogFrom.getLoanApplyNo());
		}
		if (!StringUtil.isEmpty(bankTransLogFrom.getBankType())) {
			sql.append(" AND OBTL.BANK_TYPE = ? ");
			pageQuery.addParam(bankTransLogFrom.getBankType());
		}
		if (!StringUtil.isEmpty(bankTransLogFrom.getIsSuccess())) {
			sql.append(" AND OBTL.RESULT_TYPE = ? ");
			pageQuery.addParam(bankTransLogFrom.getIsSuccess());
		}
		sql.append(" ORDER BY OBTL.START_TIME DESC ");
		pageQuery.setQuerySql(sql.toString());
		return loanDao.findDataWithPage(pageQuery);
	}

	/**
	 * 申报流程check修改主表状态
	 * 
	 * @param loanapply
	 */
	@Transactional(transactionManager = "o2oTransactionManager")
	public int updateWorkFlowNodeTranfer(O2oLoanapplyEntity loanapply) {
		return loanDao.updateById(loanapply);
	}

	/**
	 * get user loan not repay end count
	 * 
	 * @param loan_user_id
	 * @return
	 */
	public int getUserLoanCount(String loan_user_id) {
			String sql = "SELECT COUNT(1) FROM O2O_LOANAPPLY OL WHERE  OL.LOAN_ID = ? AND OL.WF_TYPE NOT IN (?,?,?) and OL.loan_type in('BK','BP','BP_JX','SG','YC','SC')";
		return loanDao.findInteger(sql, loan_user_id,StatusContexts.O2O_REPAY_END, StatusContexts.O2O_FK_OVER,StatusContexts.O2O_SB_FAIL);
	}

	/**
	 * get OA user loan not repay end count
	 * 
	 * @param idCard
	 * @return
	 */
	public int getUserOaLoanCount(String idCard) {
		String sql = "SELECT COUNT(1) FROM EADB.O2O_LOANER OL JOIN EADB.O2O_REFUND ORR ON OL.LOANAPPLYNO=ORR.LOANAPPLYNO WHERE ORR.BILLSTATUS NOT IN(?,?) AND ORR.ISLOCK=? AND OL.IDCARD = ?";
		return oaApplyDao.findInteger(sql, StatusContexts.O2O_BANK_CLO, StatusContexts.O2O_BANK_REJ, StatusContexts.O2O_NO, idCard);
	}

	/**
	 * get can user continue loan count
	 * 
	 * @param user_id
	 * @return
	 */
	public int getUserLoanContinueCount(String user_id, String loan_type) {
		// SELECT COUNT(1) FROM O2O_LOANAPPLY OL WHERE OL.WF_TYPE NOT IN
		// ('O','HN') AND OL.LOAN_ID=?
		String sql = "SELECT COUNT(1) FROM O2O_LOANAPPLY OL WHERE OL.WF_TYPE NOT IN ('O','HN','T','SN')  AND  OL.LOAN_ID=?  and OL.loan_type in ("+loan_type+")";
		return loanDao.findInteger(sql, user_id);
	}

	// /**
	// * 是否已生成过
	// * @param user_id
	// * @param bill_code
	// * @param loan_type
	// * @return
	// */
	// public int getUserLoanCountSC(String bill_code,String loan_type) {
	// //SELECT COUNT(1) FROM O2O_LOANAPPLY OL WHERE OL.WF_TYPE NOT IN
	// ('O','HN') AND OL.LOAN_ID=?
	// String sql = "SELECT OL.loan_type,OL.* FROM O2O_LOANAPPLY OL WHERE
	// OL.continue_loan_paren_no=? and OL.loan_type=? ";
	// return loanDao.findInteger(bill_code,loan_type);
	// }
	/**
	 * get user spouse can continue loan count
	 * 
	 * @param user_id
	 * @return
	 */
	public int getUserSpouseCanContinueLoanCount(String user_id, String loan_type) {
		String sql = "SELECT COUNT(1) FROM O2O_LOANAPPLY OL WHERE OL.LOANAPPLY_NO IN (SELECT OLU.LOANAPPLY_NO FROM O2O_LOAN_USER OLU WHERE OLU.SPOUSE_ID=?) AND OL.WF_TYPE NOT IN ('O','HN','T','SN') and OL.loan_type in ("+loan_type+")";
		return loanDao.findInteger(sql, user_id);
	}

	/**
	 * get OA user loan not repay end count
	 * 
	 * @param idCard
	 * @return
	 */
	public String getUserId(String billCode) {
		String sql = "SELECT OL.LOAN_ID FROM O2O_LOANAPPLY OL WHERE BILL_CODE = ?";
		return loanDao.findString(sql, billCode);
	}

	public List<RefundLine> queryRefundLine(String idcard, String idType, String name, String phoneNumber) {
		logger.info("身份证号为：" + idcard + "的用户查询还款计划表开始。");
		List<RefundLine> refundLineList = new ArrayList<RefundLine>();
		StringBuffer sql_ = new StringBuffer();
		/*sql_.append(" select rl.TERMNO,rl.INTERESTDATE,rl.ARPRINCIPAL,rl.ARINTEREST,LOANAMOUNT,rd.LOANTERM,rd.LOADAPPLYCODE ");
		sql_.append(" from eadb.O2O_REFUNDLINE rl left join eadb.O2O_REFUND rd ");
		sql_.append("  on rd.refundno = rl.refundno left join eadb.O2o_Loaner lr  on rd.loanapplyno = lr.loanapplyno ");
		sql_.append("  where lr.idcard = ?  and lr.idcardtype = ? and rd.billstatus  = 'RUN' ");
		if (StringUtils.isNotEmpty(name)) {
			sql_.append("  and lr.loanername = '" + name + "'");
		}
		if (StringUtils.isNotEmpty(phoneNumber)) {
			sql_.append("  and lr.mobile = '" + phoneNumber + "'");
		}
		sql_.append("  order by rl.TERMNO asc ");
		List<Object[]> i = oaApplyDao.findObjectsList(sql_.toString(), idcard, idType);
		if (i.size() <= 0) {// 老系统中没有还款计划表查询新系统
*/			logger.info("身份证号为：" + idcard + "的用户在老系统未找到还款计划表。");
			sql_.setLength(0);
			sql_.append(" select periods,payments_date,pay_principal,pay_interest,ol.bank_amount,ol.loan_term,ol.bill_code from o2o_payments_plan opp " + " LEFT JOIN o2o_loanapply ol on opp.bill_code = ol.bill_code " + " LEFT JOIN o2o_loan_user ou on ou.loanapply_no = ol.loanapply_no "
					+ " where  ou.document_no = ?");
			if (StringUtils.isNotEmpty(name)) {
				sql_.append(" and ou.user_full_name = '" + name + "'");
			}
			if (StringUtils.isNotEmpty(phoneNumber)) {
				sql_.append(" and ou.user_phone = '" + phoneNumber + "'");
			}
			sql_.append(" and ol.wf_type = 'O' order by periods asc ");

			List<Object[]>  i = loanDao.findObjectsList(sql_.toString(), idcard);
//		}

		if (i.size() < 0) {
			logger.info("身份证号为：" + idcard + "的用户在新系统未找到还款计划表。");
		} else {
			// 封装数据
			for (int j = 0; j < i.size(); j++) {
				Object[] aa = i.get(j);
				RefundLine refundLine = new RefundLine();
				if (aa[0] instanceof BigDecimal) {
					refundLine.setTermno(((BigDecimal) aa[0]).longValue());
				}
				if (aa[0] instanceof Integer) {
					refundLine.setTermno(((Integer) aa[0]).longValue());
				}
				refundLine.setInterestdate((Date) aa[1]);
				refundLine.setArprincipal((BigDecimal) aa[2]);
				refundLine.setArinterest((BigDecimal) aa[3]);
				refundLine.setLoanAmount((BigDecimal) aa[4]);
				if (aa[5] instanceof BigDecimal) {
					refundLine.setLoanterm(((BigDecimal) aa[5]).intValue());
				}
				if (aa[5] instanceof Integer) {
					refundLine.setLoanterm((int) aa[5]);
				}
				refundLine.setBillCode((String) aa[6]);
				refundLineList.add(refundLine);
			}
		}
		return refundLineList;
	}

	public OverdueReportPO queryOldOverdue(String idcard, String idType) {
		logger.info("身份证号为：" + idcard + "的用户在查询逾期信息开始。");
		StringBuffer sql_ = new StringBuffer();
		OverdueReportPO overdueReportPO = new OverdueReportPO();
		sql_.append("  select sum(DEFAULTAMOUNT),sum(DEFAULTCAPITAL),sum(DEFAULTINTEREST),max(DEFAULTDATENUM),max(l.LOANAMOUNT),max(ol.LOANTERM),max(l.DISPOSALDATE) ");
		sql_.append("  from eadb.o2o_overdue_report l left join eadb.O2o_Loanapply ol on ol.billcode = l.billcode ");
		sql_.append(" left join eadb.O2o_Loaner lr on ol.loanapplyno = lr.loanapplyno ");
		sql_.append("  where l.status in ('1', '2')  and lr.idcard = ?  and lr.idcardtype = ?  and l.isnew = 'Y' group by lr.idcard ");
		List<Object[]> i = oaApplyDao.findObjectsList(sql_.toString(), idcard, idType);
		if (i.size() <= 0) {// 老系统中没有还款计划表查询新系统，新系统中还没有数据
			logger.info("身份证号为：" + idcard + "的用户在老系统未找到逾期信息。");
			sql_.setLength(0);
			// 目前新系统没有逾期信息数据
			sql_.append(" select * FROM o2o_loanapply where wf_type = 'O' and loan_id IN "); // 查询新系统的贷款数据
			sql_.append("(select loan_id from o2o_loan_user   where document_no = ? and document_type = ? GROUP BY loan_id ) ");
			O2oLoanapplyEntity o = loanDao.findEntity(sql_.toString(), O2oLoanapplyEntity.class, idcard, idType);

			// 1:先在新系统中找出还款中的单号，然后在根据单号去关联老系统中的逾期数据库，看这个单是否有逾期数据
			sql_.append(" select periods,payments_date,pay_principal,pay_interest,ol.loana_mount,ol.loan_term from o2o_payments_plan opp " + " LEFT JOIN o2o_loanapply ol on opp.bill_code = ol.bill_code " + " where id_number = ? order by periods asc ");
			i = loanDao.findObjectsList(sql_.toString(), idcard);

			if (o != null) {
				logger.info("身份证号为：" + idcard + "的用户在新系统中找到已放款的借款单。");
				overdueReportPO.setLoanAmount(o.getLoanaMount());// 贷款金额
				overdueReportPO.setLoanterm(o.getLoanTerm());// 贷款期限
			} else {
				logger.info("身份证号为：" + idcard + "的用户在新系统中未找到已放款的借款单。");
			}
		} else { // 老系统中有数据库封装老数据中的值
					// 封装数据
			logger.info("身份证号为：" + idcard + "的用户在老系统中找到逾期信息。");
			Object[] aa = i.get(0);
			overdueReportPO.setDefaultAmount((BigDecimal) aa[0]); // 逾期总额
			overdueReportPO.setDefaultCapital((BigDecimal) aa[1]);// 逾期本金
			overdueReportPO.setDefaultInterest((BigDecimal) aa[2]);// 逾期利息
			overdueReportPO.setDefaultDatenum((BigDecimal) aa[3]);// 逾期天数
			overdueReportPO.setLoanAmount((BigDecimal) aa[4]);// 贷款金额
			if (aa[5] instanceof Integer) {
				overdueReportPO.setLoanterm((int) aa[5]);// 贷款期限
			}
			if (aa[6] instanceof Date) {
				overdueReportPO.setDisposalDate((Date) aa[6]);// 贷款期限
			}
			// overdueReportPO.setTotalBack((BigDecimal)aa[6]);//未还本息
		}
		return overdueReportPO;
	}

	public OverdueReportPO queryOverdue(String billcode) {
		logger.info("贷款编号为：" + billcode + "的用户在查询逾期信息开始。");
		StringBuffer sql_ = new StringBuffer();
		OverdueReportPO overdueReportPO = new OverdueReportPO();
		sql_.append("  select sum(DEFAULTAMOUNT),sum(DEFAULTCAPITAL),sum(DEFAULTINTEREST),max(DEFAULTDATENUM),max(l.LOANAMOUNT),max(ol.LOAN_TERM),max(l.DISPOSALDATE),sum(PENALTYINTEREST) ");
		sql_.append("  from o2o_overdue_report l  ");
		sql_.append("  left join O2o_Loanapply ol  ");
		sql_.append("   on ol.bill_code = l.billcode  ");
		sql_.append("  where l.status in ('1', '2')  and l.billcode = ?   and l.isnew = 'Y' group by l.billcode ");
		//List<Object[]> i = oaApplyDao.findObjectsList(sql_.toString(), billcode);
		List<Object[]> i = loanDao.findObjectsList(sql_.toString(), billcode);
		if (i.size() <= 0) {// 老系统中没有还款计划表查询新系统，新系统中还没有数据
			logger.info("贷款编号为：" + billcode + "的用户在新系统未找到逾期信息。");

		} else { // 老系统中有数据库封装老数据中的值
					// 封装数据
			logger.info("贷款编号为：" + billcode + "的用户在新系统中找到逾期信息。");
			Object[] aa = i.get(0);
			overdueReportPO.setDefaultAmount((BigDecimal) aa[0]); // 逾期总额
			overdueReportPO.setDefaultCapital((BigDecimal) aa[1]);// 逾期本金
			overdueReportPO.setDefaultInterest((BigDecimal) aa[2]);// 逾期利息
			overdueReportPO.setDefaultDatenum((BigDecimal) aa[3]);// 逾期天数
			overdueReportPO.setLoanAmount((BigDecimal) aa[4]);// 贷款金额
			if (aa[5] instanceof Integer) {
				overdueReportPO.setLoanterm((int) aa[5]);// 贷款期限
			}
			if (aa[6] instanceof Date) {
				overdueReportPO.setDisposalDate((Date) aa[6]);// 处置时间
			}
			if (aa[7] instanceof BigDecimal) {
				overdueReportPO.setPenaltyInterest((BigDecimal) aa[7]);// 罚息合计
			}

		}
		return overdueReportPO;
	}

	/**
	 * dubbo接口查询是否可以编辑用户信息
	 * 
	 * @return
	 */
	public int findIsUserEditInfo(String loan_id) {
		String sql = "SELECT COUNT(1) FROM O2O_LOANAPPLY OL WHERE OL.LOANAPPLY_NO IN (SELECT OLU.LOANAPPLY_NO FROM O2O_LOAN_USER OLU WHERE OLU.SPOUSE_ID=? OR OLU.LOAN_ID=?) AND OL.WF_TYPE NOT IN (?,?,?,?)";
		return loanDao.findInteger(sql, loan_id, loan_id, StatusContexts.O2O_NEW, StatusContexts.O2O_FK_END, StatusContexts.O2O_REPAY_END, StatusContexts.O2O_FK_OVER);
	}

	/**
	 * @Description:
	 *               <p>
	 *               向银行放款数据表中插入数据和修改主表放款时间
	 *               </p>
	 * @author CXW
	 * @date 2016年9月7日 上午10:27:36
	 * @version V1.0
	 * @param
	 * @return void
	 */
	@Transactional("o2oTransactionManager")
	public Map<String, String> updateBankData(O2oLoanForm o2oloanForm) {
		return o2oLoanDao.saveLoan(o2oloanForm);
	}

	/**
	 * @Description:
	 *               <p>
	 *               付款结束，向银行放款数据表中插入数据
	 *               </p>
	 * @author CXW
	 * @date 2016年9月7日 上午10:27:36
	 * @version V1.0
	 * @param
	 * @return void
	 */
	public Map<String, String> saveBankLoan(O2oLoanForm o2oloanForm) {
		return o2oLoanDao.saveBankLoan(o2oloanForm);
	}

	/**
	 * 新系统借款单配偶查询 是否有代偿
	 * 
	 * @param loanapplyNo
	 * @param loanId
	 * @return
	 */
	public PageResult<Object[]> findOtherLoanapply(String loanapplyNo, String loanId) {
		logger.info("新系统借款人和配偶是否包含代偿:单号[{}]", loanapplyNo);
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, 30, 0);
		O2oLoanUserEntity userEntity = o2oLoanUserDao.findSpouseId(loanId, loanapplyNo);
		StringBuffer sb = new StringBuffer("SELECT T1.BILL_CODE,T1.LOAN_TYPE," 
				+ "(SELECT T3.LOAN_AMOUNT FROM O2O_BANK_LOAN_DATA T3 WHERE T3.BILL_CODE = T1.BILL_CODE) AS LOAN_AMOUNT,"
				+ "(SELECT T2.PAY_AMOUNT FROM O2O_PAY T2 WHERE T1.LOANAPPLY_NO = T2.LOANAPPLYID AND T2.PAY_TYPE = 'DF') AS PAY_AMOUNT," 
				+ "T1.PAYMENT_TIME FROM O2O_LOANAPPLY T1 WHERE 1=1 AND T1.LOANAPPLY_NO!=?");
		if (null != userEntity && null != userEntity.getLoanId()) {
			sb.append(" AND T1.LOAN_ID IN( ?,?)  ORDER BY T1.CREATE_DATE");
			sqlPageQuery.addParam(loanapplyNo);
			sqlPageQuery.addParam(loanId);
			sqlPageQuery.addParam(userEntity.getLoanId());
		} else {
			sb.append(" AND T1.LOAN_ID =?  ORDER BY T1.CREATE_DATE");
			sqlPageQuery.addParam(loanapplyNo);
			sqlPageQuery.addParam(loanId);
		}
		sqlPageQuery.setQuerySql(sb.toString());
		return o2oLoanDao.findDataWithPage(sqlPageQuery);
	}

	/**
	 * 银行审查节点动态表单查询是否代偿（包含旧系统和老系统）
	 * 
	 * @return
	 */
	public int otherLoanapplyCount(String loanapplyNo, String loanId) {
		logger.info("查询银行审查节点动态表单查询是否代偿（包含旧系统和老系统)：单号[{}]", loanapplyNo);
		String sql = "SELECT COUNT(1) FROM o2o_loanapply t JOIN o2o_pay t1 ON t.loanapply_no = t1.loanapplyid  WHERE t1.pay_type = 'DF' AND t.loan_id=? AND t.loanapply_no!=? ";
		O2oLoanUserEntity userEntity = o2oLoanUserDao.queryLoanpplyNo(loanapplyNo, loanId);
		O2oLoanUserEntity spouseEntity = o2oLoanUserDao.findSpouseId(loanId, loanapplyNo);
		int newNum=0;
		if (null != spouseEntity && null != spouseEntity.getDocumentNo()) {
			newNum=o2oLoanDao.findInteger(sql, loanapplyNo, spouseEntity.getLoanId());
		} else {
			newNum=o2oLoanDao.findInteger(sql, loanapplyNo, loanId);
		}
		logger.info("查询银行审查节点动态表单查询逾期（包含旧系统和老系统)：单号[{}]", loanapplyNo);
		List<Object> billCodes=this.findBillCodes(userEntity,spouseEntity);
		PageResult<Object[]> result=dataCenterDao.queryOverduelist(billCodes);
		return newNum+result.getReturnDataSize();
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               新check逾期
	 *               </p>
	 * @author CXW
	 * @date 2017年1月12日 下午5:13:38
	 * @version V1.0
	 */
	public int findOverLoan(String loanapplyNo, String loanId){
		//查询借款逾期条数
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*) from o2o_overdue_report oor join o2o_loanapply ol ");
		sql.append("on ol.loanapply_no = oor.loanapplyno where ol.loanapply_no=? and ol.loan_id=? ");
		int loanUser_count = o2oLoanDao.findInteger(sql.toString(), loanapplyNo, loanId);
		//查询借款配偶逾期条数
		StringBuffer sql_spouse = new StringBuffer();
		sql_spouse.append("select count(*) from o2o_overdue_report oor join ");
		sql_spouse.append("(SELECT ol.loanapply_no,olu.spouse_id from o2o_loanapply ol join o2o_loan_user olu ");
		sql_spouse.append("on ol.loanapply_no = olu.loanapply_no where ol.loan_id = olu.loan_id) as l_t ");
		sql_spouse.append("on l_t.loanapply_no = oor.loanapplyno where oor.loanapplyno=? and l_t.spouse_id=? ");
		int loanSpouse_count = o2oLoanDao.findInteger(sql_spouse.toString(), loanapplyNo, loanId);
		return loanUser_count + loanSpouse_count;
	}
	
	
	/**
	 * @Description:
	 *               <p>
	 *               新check代偿
	 *               </p>
	 * @author yy
	 * @date 2017年3月08日 下午5:13:38
	 * @version V1.0
	 */
	public int findDCLoan(String loanId){
		//查询借款逾期条数
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT count(*) FROM o2o_ea_loan_data oe join  o2o_loanapply ol on oe.bill_code_ea = ol.bill_code where ol.loan_id =? ");
		int loanUser_count = o2oLoanDao.findInteger(sql.toString(), loanId);
		//查询借款逾期条数
		StringBuffer sql_yq = new StringBuffer();
		sql_yq.append(" select count(*) from o2o_overdue_report_interim t where t.billcode in (select ol.bill_code from o2o_loanapply ol  where ol.loan_id=? )");
		int loanUser_count_yq = o2oLoanDao.findInteger(sql_yq.toString(), loanId);
		//查询借款配偶逾期条数
		StringBuffer sql_spouse = new StringBuffer();
		sql_spouse.append(" SELECT count(*) from o2o_ea_loan_data oe LEFT JOIN o2o_loanapply ol on ");
		sql_spouse.append("oe.bill_code_ea = ol.bill_code join o2o_loan_user olu on ol.loanapply_no = olu.loanapply_no where olu.spouse_id=?");
		int loanSpouse_count = o2oLoanDao.findInteger(sql_spouse.toString(), loanId);
		//查询借款配偶逾期条数
		StringBuffer sql_spouse_yq = new StringBuffer();
		sql_spouse_yq.append(" SELECT count(*) from o2o_overdue_report_interim t where t.billcode in(SELECT ol.bill_code from o2o_loanapply ol join o2o_loan_user olu ");
		sql_spouse_yq.append(" on ol.loanapply_no = olu.loanapply_no where olu.spouse_id=? ) ");
		int loanSpouse_count_yq = o2oLoanDao.findInteger(sql_spouse_yq.toString(), loanId);
		return loanUser_count + loanSpouse_count + loanUser_count_yq + loanSpouse_count_yq;
	}
	
	/**
	 * 旧系统借款人和配偶是否包含代偿
	 * 
	 * @param loanapplyNo
	 * @param loanId
	 * @return
	 */
	public PageResult<Object[]> findOldLoanapply(String loanapplyNo, String loanId) {
		logger.info("旧系统借款人和配偶是否包含代偿:单号[{}]", loanapplyNo);
		SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, 30, 0);
		O2oLoanUserEntity userEntity = o2oLoanUserDao.queryLoanpplyNo(loanapplyNo, loanId);
		O2oLoanUserEntity spouseEntity = o2oLoanUserDao.findSpouseId(loanId, loanapplyNo);
		StringBuffer sb = new StringBuffer("SELECT T.BILLCODE,T.LOAN_TYPE,(SELECT D.BANKLOANAMOUNT FROM EADB.O2O_REFUND D WHERE T.LOANAPPLYNO=D.LOANAPPLYNO AND D.ISLOCK='N') AS  BANKLOANAMOUNT,(SELECT L.PAYAMOUNT FROM EADB.O2O_PAY L WHERE T.LOANAPPLYNO=L.LOANAPPLYID AND L.ISDEL='N')AS PAYAMOUNT,"
				+ "(SELECT D.LOADDATE FROM EADB.O2O_REFUND D WHERE T.LOANAPPLYNO=D.LOANAPPLYNO AND D.ISLOCK='N')AS LOADDATE FROM EADB.O2O_LOANAPPLY T,EADB.O2O_LOANER Y " + " WHERE T.LOANAPPLYNO=Y.LOANAPPLYNO  AND T.ISLOCK='N' AND Y.IDCARD=? ");
		sqlPageQuery.addParam(userEntity.getDocumentNo());
		if (null != spouseEntity && null != spouseEntity.getDocumentNo()) {
			sb.append(" UNION SELECT T.BILLCODE,T.LOAN_TYPE,(SELECT D.BANKLOANAMOUNT FROM EADB.O2O_REFUND D WHERE T.LOANAPPLYNO=D.LOANAPPLYNO AND D.ISLOCK='N') AS  BANKLOANAMOUNT,(SELECT L.PAYAMOUNT FROM EADB.O2O_PAY L WHERE T.LOANAPPLYNO=L.LOANAPPLYID AND L.ISDEL='N')AS PAYAMOUNT,"
					+ "(SELECT D.LOADDATE FROM EADB.O2O_REFUND D WHERE T.LOANAPPLYNO=D.LOANAPPLYNO AND D.ISLOCK='N')AS LOADDATE FROM EADB.O2O_LOANAPPLY T,EADB.O2O_LOANER Y  WHERE T.LOANAPPLYNO=Y.LOANAPPLYNO  AND T.ISLOCK='N' AND Y.IDCARD=?");
			sqlPageQuery.addParam(spouseEntity.getLoanId());
		}
		sqlPageQuery.setQuerySql(sb.toString());
		return oaApplyDao.findDataWithPage(sqlPageQuery);
	}

	/**
	 * 主表标记是否代偿状态
	 */
	public void updateDcFlag(String loanapplyNo,String type) {
		O2oLoanapplyEntity o2oLoanapplyEntity = this.findById(loanapplyNo);
		o2oLoanapplyEntity.setDcFlag(type);
		this.updateEntityById(o2oLoanapplyEntity);
	}
	/**
	 * 统计夫妻贷款单编号
	 * @param loanapplyNo
	 * @return
	 */
	public List<Object> findBillCodes(O2oLoanUserEntity userEntity,O2oLoanUserEntity spouseEntity ) {
		List<Object> billCodeList=new ArrayList<>();
		List<O2oLoanapplyEntity> newLoanapplyList=new ArrayList<O2oLoanapplyEntity>();
		List<OaBillCodeEntity> oldLoanapplyList=new ArrayList<OaBillCodeEntity>();
		StringBuffer newsql=new StringBuffer("SELECT * FROM O2O_LOANAPPLY T WHERE  T.LOAN_ID in(?");
		if(null != spouseEntity && null!=spouseEntity.getLoanId()){
			newsql.append(",?)");
			newLoanapplyList=o2oLoanDao.findEntitys(newsql.toString(), O2oLoanapplyEntity.class, userEntity.getLoanId(),spouseEntity.getLoanId());
		}else{
			newsql.append(" )");
			newLoanapplyList=o2oLoanDao.findEntitys(newsql.toString(), O2oLoanapplyEntity.class, userEntity.getLoanId());
		}
		StringBuffer oldsql = new StringBuffer("SELECT F.BILLCODE FROM (SELECT T.BILLCODE,T.LOAN_TYPE,(SELECT D.BANKLOANAMOUNT FROM EADB.O2O_REFUND D WHERE T.LOANAPPLYNO=D.LOANAPPLYNO AND D.ISLOCK='N') AS  BANKLOANAMOUNT,(SELECT L.PAYAMOUNT FROM EADB.O2O_PAY L WHERE T.LOANAPPLYNO=L.LOANAPPLYID AND L.ISDEL='N')AS PAYAMOUNT,"
				+ "(SELECT D.LOADDATE FROM EADB.O2O_REFUND D WHERE T.LOANAPPLYNO=D.LOANAPPLYNO AND D.ISLOCK='N')AS LOADDATE FROM EADB.O2O_LOANAPPLY T,EADB.O2O_LOANER Y " + " WHERE T.LOANAPPLYNO=Y.LOANAPPLYNO  AND T.ISLOCK='N' AND Y.IDCARD=? ");
		if (null != spouseEntity && null != spouseEntity.getDocumentNo()) {
			oldsql.append(" UNION SELECT T.BILLCODE,T.LOAN_TYPE,(SELECT D.BANKLOANAMOUNT FROM EADB.O2O_REFUND D WHERE T.LOANAPPLYNO=D.LOANAPPLYNO AND D.ISLOCK='N') AS  BANKLOANAMOUNT,(SELECT L.PAYAMOUNT FROM EADB.O2O_PAY L WHERE T.LOANAPPLYNO=L.LOANAPPLYID AND L.ISDEL='N')AS PAYAMOUNT,"
					+ "(SELECT D.LOADDATE FROM EADB.O2O_REFUND D WHERE T.LOANAPPLYNO=D.LOANAPPLYNO AND D.ISLOCK='N')AS LOADDATE FROM EADB.O2O_LOANAPPLY T,EADB.O2O_LOANER Y  WHERE T.LOANAPPLYNO=Y.LOANAPPLYNO  AND T.ISLOCK='N' AND Y.IDCARD=?");
			oldsql.append(")F ");
			oldLoanapplyList=oaApplyDao.findEntitys(oldsql.toString(), OaBillCodeEntity.class, userEntity.getDocumentNo(),spouseEntity.getLoanappNo());
		}else{
			oldsql.append(")F ");
			oldLoanapplyList=oaApplyDao.findEntitys(oldsql.toString(), OaBillCodeEntity.class, userEntity.getDocumentNo());
		}
		if(null!=oldLoanapplyList && oldLoanapplyList.size()>0){
			for (OaBillCodeEntity oaBillCodeEntity: oldLoanapplyList) {
				billCodeList.add(oaBillCodeEntity.getBillCode());
			}
		}
		if(null!=newLoanapplyList && newLoanapplyList.size()>0){
			for (O2oLoanapplyEntity o2oLoanapplyEntity : newLoanapplyList) {
				billCodeList.add(o2oLoanapplyEntity.getBillCode());
			}
		}
		return billCodeList;
	}
	/**
	 * 查询夫妻 loanId
	 * @param loanapplyNo
	 * @param loanId
	 * @return
	 */
	public List<Object> findLoanIds(String loanapplyNo, String loanId) {
		logger.info("查询夫妻LoanId,loanapplyNo:[{}],loanId:[{}]", loanapplyNo,loanId);
		O2oLoanUserEntity userEntity = o2oLoanUserDao.queryLoanpplyNo(loanapplyNo, loanId);
		O2oLoanUserEntity spouseEntity = o2oLoanUserDao.findSpouseId(loanId, loanapplyNo);
		List<Object> billCodes=this.findBillCodes(userEntity,spouseEntity);
		return billCodes;
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               根据LTD查询省区
	 *               </p>
	 * @author CXW
	 * @date 2016年12月7日 上午9:30:43
	 * @version V1.0
	 */
	public OaLoanProvinceEntity findProvince(String ltd){
		String sql = "SELECT PD.ITEMCODE as province,PD.ITEMNAME as provinceName FROM EADB.HR_UNITS HU JOIN EADB.PUB_DDITEM PD ON HU.PROVINCE=PD.ITEMCODE WHERE HU.UNITNO=? AND PD.ISLOCK='N' AND PD.DDNO=159";
		return oaApplyDao.findEntity(sql, OaLoanProvinceEntity.class, ltd);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               根据贷款单号主ID查询贷款基本信息
	 *               </p>
	 * @author CXW
	 * @date 2016年12月9日 下午1:52:49
	 * @version V1.0
	 */
	public OaLoanApplyEntity findOaLoanById(String loanApplyNo){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT OL.LOANAPPLYNO,OL.LOADAPPLYCODE,OL.ISLOCK,OL.LOANAMOUNT,OL.LOANTERM, ");
		sql.append("OL.BANKLOANAMOUNT,OL.LTD,OL.BANKTYPE,OLU.LOANERNAME,OLU.IDCARDTYPE,OLU.IDCARD ");
		sql.append(",HU.UNITCODE,HU.NAMECN,OL.RATE,OL.BANKREFUNDCODE  FROM EADB.O2O_REFUND OL JOIN EADB.O2O_LOANER OLU ");
		sql.append("ON OL.LOANAPPLYNO = OLU.LOANAPPLYNO JOIN EADB.HR_UNITS HU ON HU.UNITNO = OL.LTD ");
		sql.append("WHERE OL.ISLOCK='N' AND OL.LOANAPPLYNO=?");
		return oaApplyDao.findEntity(sql.toString(), OaLoanApplyEntity.class, loanApplyNo);
	}
	
	public O2oZxEntity findO2oZxById(String zxId) {
		logger.debug("find loan by id,id={}", zxId);
		String sql = "select * from o2o_zx where id=?";
		return loanDao.findEntity(sql, O2oZxEntity.class, zxId);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               根据贷款编号CODE查询贷款基本信息
	 *               </p>
	 * @author CXW
	 * @date 2016年12月9日 下午1:52:49
	 * @version V1.0
	 */
	public OaLoanApplyEntity findOaLoanByCode(String billCode){
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT OL.LOANAPPLYNO,OL.LOADAPPLYCODE,OL.ISLOCK,OL.LOANAMOUNT,OL.LOANTERM, ");
		sql.append("OL.BANKLOANAMOUNT,OL.LTD,OL.BANKTYPE,OLU.LOANERNAME,OLU.IDCARDTYPE,OLU.IDCARD ");
		sql.append(",HU.UNITCODE,HU.NAMECN,OL.RATE FROM EADB.O2O_REFUND OL JOIN EADB.O2O_LOANER OLU ");
		sql.append("ON OL.LOANAPPLYNO = OLU.LOANAPPLYNO JOIN EADB.HR_UNITS HU ON HU.UNITNO = OL.LTD ");
		sql.append("WHERE OL.ISLOCK='N' AND OL.LOADAPPLYCODE=?");
		return oaApplyDao.findEntity(sql.toString(), OaLoanApplyEntity.class, billCode);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               微信传 手机号、姓名、身份证号 查询 最新贷款单进度
	 *               </p>
	 * @author CXW
	 * @date 2016年12月30日 上午11:27:38
	 * @version V1.0
	 */
	public String findLoanApply(WeChatParams chatParams) {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT T1.WF_TYPE FROM ( ");
		sql.append(" SELECT (SELECT PDO.ITEM_NAME FROM PUB_DDITEM_O2O PDO ");
		sql.append(" WHERE PDO.DD_NO='2' AND PDO.ITEM_CODE=OL.WF_TYPE) AS WF_TYPE ");
		sql.append(" ,OLU.USER_FULL_NAME,OLU.DOCUMENT_NO,OLU.USER_PHONE ");
		sql.append(" ,MAX(OL.CREATE_DATE) FROM O2O_LOANAPPLY OL RIGHT JOIN ");
		sql.append(" O2O_LOAN_USER OLU ON OL.LOAN_ID = OLU.LOAN_ID ");
		sql.append(" WHERE OL.LOANAPPLY_NO=OLU.LOANAPPLY_NO ");
		if (!StringUtils.isEmpty(chatParams.getIdCard())) {
			sql.append(" AND OLU.DOCUMENT_NO = ? ");
			pageQuery.addParam(chatParams.getIdCard());
		}
		if (!StringUtils.isEmpty(chatParams.getPhone())) {
			sql.append(" AND OLU.USER_PHONE = ? ");
			pageQuery.addParam(chatParams.getPhone());
		}
		if (!StringUtils.isEmpty(chatParams.getName())) {
			sql.append(" AND OLU.USER_FULL_NAME = ? ");
			pageQuery.addParam(chatParams.getName());
		}
		sql.append(" ) AS T1 ");
		pageQuery.setQuerySql(sql.toString());
		PageResult<Object[]> pageResult = loanDao.findDataWithPage(pageQuery);
		String typeStatus = "";
		if (pageResult.getData() != null) {
			List<Object[]> list = pageResult.getData();
			if (list.get(0)[0] != null)
				typeStatus = list.get(0)[0].toString();
		}
		return typeStatus;
	}

	/**
	 * 根据借款人id寻找，借款人最近的一笔贷款单
	 * @param billCode
	 * @return
	 */
	public O2oLoanapplyEntity fingLoanapplyNewByloanId(String loanid) {
		String sql = "select * from o2o_loanapply where  create_date = (select MAX(create_date) from o2o_loanapply where loan_id = ? and islock='N' and loan_flg='Y') ";
		return loanDao.findEntity(sql, O2oLoanapplyEntity.class, loanid);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               根据用户id查询所有贷款单号信息
	 *               </p>
	 * @author CXW
	 * @date 2017年6月16日 下午3:35:10
	 * @version V1.0
	 */
	public List<O2oLoanapplyEntity> findByLoanId(String loanId){
		String sql = "select ol.* from o2o_loanapply ol where ol.wf_type='T' and ol.loan_id=?";
		return loanDao.findEntitys(sql, O2oLoanapplyEntity.class, loanId);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               loan-11722 增加业务汇总统计功能
	 *               </p>
	 * @author CXW
	 * @date 2017年6月23日 下午1:57:29
	 * @version V1.0
	 */
	public PageResult<Object[]> findLoanApplyStatis() {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		String querySql = "SELECT * from o2o_loanapply_statistics ORDER BY total_sum desc";
		pageQuery.setSumColumnIndexs(new int[]{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18});
		pageQuery.setQuerySql(querySql);
		return loanDao.findDataWithPage(pageQuery);
	}
	/**
	 * @Description:
	 *               <p>
	 *               loan-11722 增加业务汇总统计功能导出
	 *               </p>
	 * @author CXW
	 * @date 2017年6月23日 下午1:57:29
	 * @version V1.0
	 */
	public PageResult<Object[]> findLoanApplyStatiseExcel() {
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<>(Object[].class, Integer.MAX_VALUE, 0);
		String querySql = "SELECT regional_name,new_count,new_sum,z_count,z_sum,zf_count,zf_sum,n_count,n_sum,nf_count,nf_sum,s_count,s_sum,total_count,total_sum,m_count,m_sum,y_count,y_sum  from o2o_loanapply_statistics ORDER BY total_sum desc";
		pageQuery.setSumColumnIndexs(new int[]{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18});
		pageQuery.setQuerySql(querySql);
		return loanDao.findDataWithPage(pageQuery);
	}
	/**
	 * @Description:
	 *               <p>
	 *               获取借款人扩展信息
	 *               </p>
	 * @author CXW
	 * @date 2017年7月7日 上午10:04:55
	 * @version V1.0
	 */
	public CustomerBasicInfo getCustomerEntityById(String loanId){
		return loanDao.getById(CustomerBasicInfo.class, loanId);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               获取借款人扩展信息 脱敏
	 *               </p>
	 * @author LMX
	 * @date 2018年3月6日 下午3:16:39
	 */
	public CustomerBasicInfo getCustomerEntityById_sensitive(String loanId){
		return loanDao.getById_sensitive(loanId);
	}
	
	/**
	 * @Description:
	 *               <p>
	 *               根据身份证查询用户loanId
	 *               </p>
	 * @author CXW
	 * @date 2017年7月14日 上午11:23:00
	 * @version V1.0
	 */
	public O2oLoanUserEntity getLoanId(String idCard){
		String sql = "select * from o2o_loan_user u where  u.document_no=? GROUP BY u.loan_id";
		return loanDao.findEntity(sql, O2oLoanUserEntity.class, idCard);
	}

	/**@Description:
	 *               <p>
	 *               修改贷款要素
	 *               </p>
	 * @author LMX
	 * @date 2017年9月7日 上午10:01:34
	 * @version V1.0
	 * @param vo
	 * @return
	 */
	public ModelResult<O2oLoanapplyEntity> udpateEntity(LoanApplyFileForm vo) {

		ModelResult<O2oLoanapplyEntity> modelResult =new ModelResult<>();
		O2oLoanapplyEntity loanapplyEntity = loanDao.getById(O2oLoanapplyEntity.class, vo.getLoanapplyNo());
		loanapplyEntity.setRepayMentway(vo.getRepayMentway());// 还款方式
		loanapplyEntity.setLoanTerm(Integer.parseInt(vo.getLoanTerm()));// 申请期限
		loanapplyEntity.setGuarAnteeWay(vo.getGuarAnteeWay());// 担保方式
		loanapplyEntity.setReferrerName(vo.getReferrerName());// 内部推荐人
		loanapplyEntity.setCommiceRecommend(vo.getCommiceRecommend());// 外部推荐方\人
		loanapplyEntity.setRemark(vo.getRemark());// 备注
		User user = UserHolder.getUser();
		if (user != null) {
			loanapplyEntity.setEndRo(user.getNo());// 最后更新人
			loanapplyEntity.setEndDate(new Date());// 最后更新日期
		}
		boolean result = loanDao.udpateEntity(loanapplyEntity);
		if(!result){
			return modelResult.withError(null,"修改失败");
		}
		try {
			CustOpLogEntity custOpLogEntity =new CustOpLogEntity();
			custOpLogEntity.setOpPsn(user.getCname());
			custOpLogEntity.setOpId(user.getNo());
			custOpLogEntity.setOpType(CustOpLogEntity.UPDATE_OP_TYPE);
			custOpLogService.saveLog(custOpLogEntity, loanapplyEntity);
		} catch (Exception e) {
			logger.info("客户修改日志异常:[{}]", e);
		}
		modelResult.setModel(loanapplyEntity);
		return modelResult;
	}
	
	/**
	 * @Title: findO2oLoanapplyEntityByZxNo
	 * @Description: 根据征信报告id，查询新建中的征信准入信息
	 * @author 张庆奇
	 * @param ZxNo
	 * @return
	 * @return O2oLoanapplyEntity
	 * @throws
	 * @date 2017年10月17日 下午5:31:40
	 */
	public O2oLoanapplyEntity findO2oLoanapplyEntityByZxNo(String ZxNo){
		String sql="select ly.* from o2o_loanapply ly,o2o_loan_user u where ly.loanapply_no=u.loanapply_no and ly.wf_type='NEW' and zx_no=? group by ly.loanapply_no " ;
				
		return loanDao.findEntity(sql, O2oLoanapplyEntity.class, ZxNo);
	}
	/**
	 * 放款更客户所有单的第几笔放款
	 * @param loanid
	 */
	public void updateO2oloanapplyBanknum(String loanid) {
		loanDao.updateO2oloanapplyBanknum(loanid);
	}

	/**@Description:
	 *               <p>
	 *               查询上一次放款时间
	 *               </p>
	 * @author LMX
	 * @date 2018年1月3日 下午2:43:24
	 * @version V1.0
	 */
	public String getLastPaymentTime(String loanapplyNo, String loanId) {
		return loanDao.getLastPaymentTime(loanapplyNo, loanId);
	}
}
